from flask import render_template, request, jsonify, redirect, url_for, flash, current_app, abort
from flask_login import login_required, logout_user, login_user, current_user
import functools
import json
import pandas as pd
from werkzeug.utils import secure_filename
from web import Web

# 权限检查装饰器
def admin_required(func):
    @login_required
    @functools.wraps(func)
    def decorated_function(*args, **kwargs):
        # 检查用户是否为管理员
        if not hasattr(current_user, 'role') or current_user.role != 'admin':
            abort(403)  # 禁止访问
        return func(*args, **kwargs)
    return decorated_function

# 导入数据库模块
from database import User, get_user_by_username, get_user_by_email, get_user_scores
from database import get_user_performance, get_all_user_scores, add_score
from database import update_score as db_update_score, delete_score as db_delete_score
from database import update_performance as db_update_performance
from flask import session

# 登录路由
def login():
    if current_user.is_authenticated:
        return redirect(url_for('index'))
    
    error_message = None
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        # 查找用户
        user = get_user_by_username(username)
        
        # 验证用户
        if user and user.check_password(password):
            login_user(user, remember=request.form.get('remember-me'))
            return redirect(url_for('index'))
        else:
            error_message = '用户名或密码错误'
    
    return render_template('login.html', error_message=error_message)

# 注册路由
def register():
    if current_user.is_authenticated:
        return redirect(url_for('index'))
    
    error_message = None
    success_message = None
    
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']
        password = request.form['password']
        confirm_password = request.form['confirm_password']
        
        # 验证用户名格式
        import re
        if not re.match(r'^[a-zA-Z0-9_]{3,20}$', username):
            error_message = '用户名必须是3-20个字符，只能包含字母、数字和下划线'
        # 检查用户名是否已存在
        elif get_user_by_username(username):
            error_message = '用户名已被使用'
        # 验证邮箱格式
        elif not re.match(r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$', email):
            error_message = '请输入有效的电子邮箱地址'
        # 检查邮箱是否已存在
        elif get_user_by_email(email):
            error_message = '电子邮箱已被注册'
        # 验证密码长度
        elif len(password) < 6:
            error_message = '密码长度至少为6个字符'
        # 验证密码一致性
        elif password != confirm_password:
            error_message = '两次输入的密码不一致'
        # 验证是否同意条款
        elif not request.form.get('terms'):
            error_message = '请阅读并同意服务条款和隐私政策'
        else:
            # 创建新用户
            new_user = User(username=username, email=email, role='user')
            new_user.set_password(password)
            from database import db
            db.session.add(new_user)
            db.session.commit()
            
            # 为新用户添加默认性能指标
            from database import PerformanceMetric
            default_performance = PerformanceMetric(
                user_id=new_user.id,
                avg_score=0,
                avg_score_change="0%",
                max_score=0,
                max_score_change="0%",
                min_score=0,
                min_score_change="0%",
                class_rank=0,
                rank_change="无变化"
            )
            db.session.add(default_performance)
            db.session.commit()
            
            # 注册成功后重定向到登录页面
            flash('注册成功！请使用新账户登录')
            return redirect(url_for('login'))
    
    return render_template('register.html', error_message=error_message, success_message=success_message)

# 注销路由
def logout():
    logout_user()
    return redirect(url_for('login'))

# 首页路由
@login_required
def index():
    from database import db, ExamProject, ExamScore
    
    # 获取所有考试项目
    exam_projects = ExamProject.query.all()
    
    # 获取最新的考试成绩（按项目ID排序，获取前10条）
    latest_scores = ExamScore.query.order_by(ExamScore.project_id.desc()).limit(10).all()
    
    # 准备项目数据列表
    projects_data = []
    for project in exam_projects:
        project_dict = project.as_dict()
        # 统计该项目下的学生数量
        student_count = ExamScore.query.filter_by(project_id=project.id).count()
        project_dict['student_count'] = student_count
        projects_data.append(project_dict)
    
    # 准备成绩数据列表
    scores_data = []
    for score in latest_scores:
        scores_dict = score.as_dict()
        # 获取对应的考试项目名称
        project = ExamProject.query.filter_by(id=score.project_id).first()
        if project:
            scores_dict['project_name'] = project.name
        scores_data.append(scores_dict)
    
    return render_template('index.html', 
                          projects=projects_data, 
                          latest_scores=scores_data, 
                          current_user=current_user)

# API - 获取成绩数据
@login_required
def api_get_scores():
    # 获取查询参数
    exam_type = request.args.get('exam_type', 'all')
    subject = request.args.get('subject', 'all')
    
    # 使用数据访问函数获取成绩
    scores = get_user_scores(current_user.id, exam_type, subject)
    
    # 转换为JSON格式
    filtered_scores = [
        {
            "exam_name": score.exam_name,
            "exam_date": score.exam_date,
            "subject": score.subject,
            "score": score.score,
            "grade": score.grade,
            "rank": score.rank
        } for score in scores
    ]
    
    return jsonify(filtered_scores)


# API - 获取图表数据
@login_required
def api_get_chart_data():
    # 使用数据访问函数获取所有成绩
    scores = get_all_user_scores(current_user.id)
    
    # 按科目分组获取最新的考试成绩
    subject_scores = {}
    for score in scores:
        if score.subject not in subject_scores:
            subject_scores[score.subject] = score.score
        else:
            # 保留最新的成绩（假设按考试日期判断）
            existing_score = subject_scores[score.subject]
            if score.score > existing_score:
                subject_scores[score.subject] = score.score
    
    # 构建图表数据
    subjects = list(subject_scores.keys())
    scores_data = list(subject_scores.values())
    
    # 为比较数据使用类似的值，但略有差异
    previous_scores = [max(0, s - 2) for s in scores_data]  # 简单地减去2作为之前的成绩
    
    chart_data = {
        "score_trend": {
            "labels": ["1月", "2月", "3月", "4月", "5月"],
            "data": [78, 80, 81, 83, 85] if not scores_data else [min(100, sum(scores_data) // len(scores_data) - 5 + i) for i in range(5)]
        },
        "subject_distribution": {
            "labels": subjects if subjects else ["数学", "语文", "英语", "物理", "化学"],
            "data": scores_data if scores_data else [92, 85, 88, 78, 72]
        },
        "score_comparison": {
            "labels": subjects if subjects else ["数学", "语文", "英语", "物理", "化学"],
            "current": scores_data if scores_data else [92, 85, 88, 78, 72],
            "previous": previous_scores if previous_scores else [88, 82, 86, 79, 74]
        }
    }
    
    return jsonify(chart_data)

# 批量查询路由 - 仅管理员可见
@admin_required
def batch_query():
    results = None
    table_data = []
    table_headers = []
    project = {}
    scores = []
    
    if request.method == 'POST':
        # 获取URL输入
        query_url = request.form.get('query_url', '').strip()
        print(query_url)
        
        # 获取文件上传
        student_file = request.files.get('student_file')
            
        # 处理文件上传
        if student_file and student_file.filename:
            # 获取文件扩展名
            import os
            file_ext = os.path.splitext(student_file.filename)[1].lower()
            
            try:
                # 处理不同类型的文件
                if file_ext in ['.xlsx', '.xls']:
                    # 处理Excel文件（需要安装pandas库）
                    try:
                        import pandas as pd
                        
                        # 读取文件内容
                        df = pd.read_excel(student_file)
 
                        file_content = df.values.tolist()
                        
                        ids = []
                        for row in file_content:
                            id = {}
                            id["xuehao"]= row[0]
                            id["name"]= row[1]
                            ids.append(id)

                        # 添加调试信息
                        flash(f'成功读取文件，包含 {len(ids)} 行数据')
                        print(ids)
                    except ImportError:
                        # 如果没有安装pandas，给出提示并跳过文件处理
                        flash('处理Excel文件需要安装pandas库，请联系管理员')

                else:
                    flash('不支持的文件格式，请仅上传Excel文件(.xlsx, .xls)')

            except Exception as e:
                flash(f'文件处理错误: {str(e)}')

        web = Web()
        project, scores = web.getScore(query_url, ids)

        if len(scores) > 0:
            results = "查询成功"
            table_headers = scores[0].keys()

            for score in scores:
                table_data.append(score.values())

        
    return render_template('batch_query.html', results=results, scores=scores, project=project, 
     table_headers=table_headers, table_data=table_data)

@admin_required
def admin_save_scores():
    try:
        data = request.json
        print(data)
        scores = data.get('scores', [])
        
        if not scores:
            return jsonify({'success': False, 'message': '没有接收到数据'})

        # print(scores)
        
        # saved_count = 0
        # from database import db, ExamScore
        
        # 尝试获取当前项目ID（可能需要从请求或会话中获取）
        # 这里暂时使用默认值1，实际使用时可能需要调整
        # project_id = 1
        
        # for score_data in scores:
        #     try:
        #         # 创建新的成绩记录
        #         new_score = ExamScore(
        #             project_id=project_id,
        #             student_xuehao=score_data.get('学号', ''),
        #             student_name=score_data.get('姓名', ''),
        #             # 填充其他字段，根据实际数据结构调整
        #             chinese_score=score_data.get('语文', ''),
        #             chinese_ranking=score_data.get('语文排名', ''),
        #             maths_score=score_data.get('数学', ''),
        #             maths_ranking=score_data.get('数学排名', ''),
        #             english_score=score_data.get('英语', ''),
        #             english_ranking=score_data.get('英语排名', ''),
        #             physics_score=score_data.get('物理', ''),
        #             physics_ranking=score_data.get('物理排名', ''),
        #             chemistry_original_score=score_data.get('化学', ''),
        #             chemistry_ranking=score_data.get('化学排名', ''),
        #             biology_original_score=score_data.get('生物', ''),
        #             biology_ranking=score_data.get('生物排名', ''),
        #             three_score=score_data.get('语数外总分', ''),
        #             three_ranking=score_data.get('语数外排名', ''),
        #             all_original_score=score_data.get('总分', ''),
        #             class_ranking=score_data.get('班级排名', ''),
        #             all_ranking=score_data.get('年级排名', '')
        #         )
                
        #         db.session.add(new_score)
        #         saved_count += 1
        #     except Exception as inner_e:
        #         print(f'处理单条记录时出错: {str(inner_e)}')
        #         # 继续处理其他记录，不中断循环
        #         continue
        
        # # 提交所有成功的记录
        # db.session.commit()
        
        return jsonify({'success': True, 'message': f'成功保存 {saved_count} 条记录'})
    except Exception as e:
        print(f'保存数据时出错: {str(e)}')
        from database import db
        db.session.rollback()
        return jsonify({'success': False, 'message': f'保存失败: {str(e)}'}), 400

# 数据管理 - 添加成绩
@admin_required
def admin_add_score():
    try:
        data = request.json
        new_score = add_score(
            user_id=current_user.id,
            exam_name=data['exam_name'],
            exam_date=data['exam_date'],
            subject=data['subject'],
            score=data['score'],
            grade=data['grade'],
            rank=data['rank']
        )
        return jsonify({'success': True, 'message': '成绩添加成功'})
    except Exception as e:
        from database import db
        db.session.rollback()
        return jsonify({'success': False, 'message': str(e)}), 400

# 数据管理 - 更新成绩
@admin_required
def admin_update_score():
    try:
        data = request.json
        # 从请求数据中获取score_id
        score_id = data.get('score_id')
        if not score_id:
            return jsonify({'success': False, 'message': '缺少score_id参数'}), 400
        score = db_update_score(score_id, current_user.id, data)
        if not score:
            return jsonify({'success': False, 'message': '成绩记录不存在'}), 404
        return jsonify({'success': True, 'message': '成绩更新成功'})
    except Exception as e:
        from database import db
        db.session.rollback()
        return jsonify({'success': False, 'message': str(e)}), 400

# 数据管理 - 删除成绩
@admin_required
def admin_delete_score():
    try:
        # 从URL参数或请求体中获取score_id
        score_id = request.args.get('score_id') or request.json.get('score_id')
        if not score_id:
            return jsonify({'success': False, 'message': '缺少score_id参数'}), 400
        success = db_delete_score(score_id, current_user.id)
        if not success:
            return jsonify({'success': False, 'message': '成绩记录不存在'}), 404
        return jsonify({'success': True, 'message': '成绩删除成功'})
    except Exception as e:
        from database import db
        db.session.rollback()
        return jsonify({'success': False, 'message': str(e)}), 400

# 数据管理 - 更新性能指标
@admin_required
def admin_update_performance():
    try:
        data = request.json
        perf = db_update_performance(current_user.id, data)
        return jsonify({'success': True, 'message': '性能指标更新成功'})
    except Exception as e:
        from database import db
        db.session.rollback()
        return jsonify({'success': False, 'message': str(e)}), 400

# 文件上传路由 - 用于保存文件并提示上传成功
@admin_required
def check_file():
    if request.method == 'POST':
        # 获取上传的文件
        student_file = request.files.get('student_file')
        
        if not student_file or not student_file.filename:
            return jsonify({'success': False, 'message': '请选择要上传的文件'})
          
        # 返回成功响应，不包含文件内容
        return jsonify({
            'success': True,
            'message': f'学生名单上传成功并已保存。'
        })

    # 如果不是POST请求，返回错误
    return jsonify({'success': False, 'message': '无效的请求方法'})

# 注册所有路由到应用
def register_routes(app):
    app.add_url_rule('/login', view_func=login, methods=['GET', 'POST'])
    app.add_url_rule('/register', view_func=register, methods=['GET', 'POST'])
    app.add_url_rule('/logout', view_func=logout)
    app.add_url_rule('/', view_func=index)
    app.add_url_rule('/batch_query', view_func=batch_query, methods=['GET', 'POST'])
    app.add_url_rule('/check_file', view_func=check_file, methods=['POST'])
    app.add_url_rule('/api/scores', view_func=api_get_scores, methods=['GET'])
    app.add_url_rule('/api/chart_data', view_func=api_get_chart_data, methods=['GET'])
    app.add_url_rule('/admin/add_score', view_func=admin_add_score, methods=['POST'])
    app.add_url_rule('/admin/save_scores', view_func=admin_save_scores, methods=['POST'])
    app.add_url_rule('/admin/update_score/<int:score_id>', view_func=admin_update_score, methods=['PUT'])
    app.add_url_rule('/admin/delete_score/<int:score_id>', view_func=admin_delete_score, methods=['DELETE'])
    app.add_url_rule('/admin/update_performance', view_func=admin_update_performance, methods=['PUT'])